import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

public class ExcelComparator {

    // 从 Excel 文件中读取所有编号
    public static Set<String> readExcelFile(String filePath) throws IOException {
        Set<String> values = new HashSet<>();

        FileInputStream fis = new FileInputStream(new File(filePath));
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0); // 只读取第一个工作表

        for (Row row : sheet) {
            Cell cell = row.getCell(0); // 只读取第一列
            if (cell != null) {
                String value = "";
                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getStringCellValue().trim();
                        break;
                    case NUMERIC:
                        value = String.valueOf((long) cell.getNumericCellValue());
                        break;
                    default:
                        continue; // 忽略其他类型
                }
                if (!value.isEmpty()) {
                    values.add(value);
                }
            }
        }

        workbook.close();
        fis.close();
        return values;
    }

    // 比较两个 Excel，找出第一个中有但第二个中没有的项
    public static List<String> compareExcelFiles(String file1Path, String file2Path) throws IOException {
        Set<String> list1 = readExcelFile(file1Path);
        Set<String> list2 = readExcelFile(file2Path);

        List<String> differences = new ArrayList<>();
        for (String val : list1) {
            if (!list2.contains(val)) {
                differences.add(val);
            }
        }

        return differences;
    }

    // 主方法示例
    public static void main(String[] args) {
        String file1Path = "C:\\Users\\23234\\Desktop\\工作簿3.xlsx";   // 第一个 Excel 文件路径
        String file2Path = "C:\\Users\\23234\\Desktop\\工作簿2.xlsx";   // 第二个 Excel 文件路径

        try {
            List<String> differences = compareExcelFiles(file1Path, file2Path);

            System.out.println("在第一个 Excel 中有但第二个中没有的编号：");
            for (String val : differences) {
                System.out.println(val);
            }

            System.out.println("共找到 " + differences.size() + " 个不同值。");

        } catch (IOException e) {
            System.err.println("发生错误：" + e.getMessage());
            e.printStackTrace();
        }
    }
}